Test data
Torbjørn Skinnemoen Ottersen
2022-07-16
Testing ideas for the Shiny app/dashboard.
Background
Libraries
if (!require("pacman")) install.packages("pacman"); library(pacman)## Loading required package: pacmanp_load(shiny,tidyverse, scales, rvest, janitor, plotly, readxl, MetBrewer)Functions
HTML
read_proff6 <- function(file, start, end){
read_html(file, encoding = "UTF-8") |>
html_node("#inner-frame") |>
html_table() |>
select( # removing empty column
REGNSKAPSPERIODE:all_of(start)) |> # using all_of()
mutate( # removing whitespace
REGNSKAPSPERIODE = str_squish(REGNSKAPSPERIODE)
) |>
filter( # removing duplicate table - no idea why it's necessary
!row_number() > 176
) |>
filter( # removing duplicate rows
!grepl("Lukk", REGNSKAPSPERIODE)
) |>
pivot_longer( # tidying data
all_of(end):all_of(start), names_to = "year") |> # using all_of()
mutate( # changing to real NAs and turning years into numbers
value = na_if(value, "-"),
year = as.integer(year)) |>
mutate( # adding currency column
valutakode = ifelse(REGNSKAPSPERIODE == "Valutakode", value, NA)
) |>
fill( # filling the currency column
valutakode, .direction = "updown"
) |>
filter( # removing dates and redundant currency
REGNSKAPSPERIODE != "Sluttdato" & REGNSKAPSPERIODE != "Startdato" &
REGNSKAPSPERIODE != "Valutakode") |>
mutate( # removing whitespace in numbers
value = str_replace_all(value, "\\s", "")
) |>
mutate( # turning into numbers
value = as.numeric(value)
) |>
mutate( # removing years as values
value = ifelse(grepl("i hele 1000", REGNSKAPSPERIODE), NA, value)
) |>
mutate( # values no longer in 1000
value = value * 1000
) |>
distinct() |> # removing any remaining duplicate rows
pivot_wider( # pivoting
names_from = REGNSKAPSPERIODE, values_from = value
) |>
clean_names() |> # tidying names
select(-lederlonn_i_hele_1000,
-resultatregnskap_i_hele_1000,
-balanseregnskap_i_hele_1000) |> # removing headings
rename( # making it clear what lonn refers to
lederlonn = lonn
) |>
arrange(desc(year)) # arranging by year
}Excel wide
widen_excel <- function(file){
read_excel(file) |>
mutate(
Value = Value * 1000
) |>
unite(
entry, c(Category, Name), sep = "_"
) |>
select(-`Sub-category`) |>
pivot_wider(names_from = entry, values_from = Value) |>
clean_names()
}Loading data
kreft_html <- read_proff6("Data/Kreftforeningen.html", "2002", "2021")
kreft_excel <- widen_excel("Data/OPXfinans_final.xlsx")Viewing data
View()
View(kreft_html)
View(kreft_excel)kabled
kreft_html| year | valutakode | lederlonn | leder_annen_godtgjorelse | sum_salgsinntekter | annen_driftsinntekt | sum_driftsinntekter | varekostnad | beholdningsendringer | lonnskostnader | herav_kun_lonn | ordinaere_avskrivninger | nedskrivning | andre_driftskostnader | driftsresultat | inntekt_pa_invest_annet_foretak_i_sm_konsern | inntekt_pa_investering_i_datterselskap | sum_annen_renteinntekt | inntekt_pa_invest_i_tilknyttet_selskap | sum_annen_finansinntekt | sum_finansinntekter | nedskrivning_fin_anleggsmidler | sum_annen_rentekostnad | andre_finanskostnader | sum_annen_finanskostnad | sum_finanskostnader | resultat_for_skatt | sum_skatt | ordinaert_resultat | ekstraordinaere_inntekter | ekstraordinaere_kostnader | skatt_ekstraordinaert | arsresultat | ordinaert_utbytte | ekstraordinaert_utbytte | tilleggsutbytte | sum_utbytte | konsernbidrag | goodwill | sum_immaterielle_midler | sum_anleggsmidler | tomter_bygninger_og_annen_fast_eiendom | maskiner_anlegg_biler | driftslosore_inventar_verktoy_biler | sum_varige_driftsmidler | aksjer_investeringer_i_datterselskap | endr_behold_varer_under_tilvirk_ferdige | investeringer_i_aksjer_og_andeler | andre_fordringer | sum_finansielle_anleggsmidler | sum_varelager | kundefordringer | konsernfordringer | sum_fordringer | sum_investeringer | kasse_bank_post | sum_kasse_bank_post | sum_omlopsmidler | sum_eiendeler | aksje_selskapskapital | annen_innskutt_egenkapital | sum_innskutt_egenkapital | sum_opptjent_egenkapital | annen_egenkapital | sum_egenkapital | sum_avsetninger_til_forpliktelser | pant_gjeld_til_kredittinstitusjoner | langsiktig_konserngjeld | ansvarlig_lanekapital | sum_annen_langsiktig_gjeld | annen_langsiktig_gjeld | sum_langsiktig_gjeld | gjeld_til_kredittinstitusjoner | leverandorgjeld | skyldig_offentlige_avgifter | kortsiktig_konserngjeld | annen_kortsiktig_gjeld | sum_kortsiktig_gjeld | sum_gjeld | sum_egenkapital_og_gjeld | garantistillelser | pantstillelser |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2021 | NOK | NA | NA | 27451000 | 505958000 | 533409000 | 103449000 | NA | NA | NA | NA | NA | 508048000 | -78088000 | NA | NA | NA | NA | NA | 156648000 | NA | NA | NA | NA | NA | 78561000 | NA | 78561000 | NA | NA | NA | 78561000 | NA | NA | NA | NA | NA | NA | NA | 20533000 | NA | NA | 14718000 | 14718000 | 150000 | NA | 35000 | 5630000 | 5815000 | NA | 6143000 | NA | 35528000 | 1423202000 | 238371000 | 238371000 | 1697102000 | 1717635000 | 1e+08 | NA | 100000000 | 909398000 | 909398000 | 1009398000 | 368635000 | NA | NA | NA | NA | NA | 368635000 | NA | 14563000 | 11428000 | NA | 313612000 | 339602000 | 708237000 | 1717635000 | NA | NA |
| 2020 | NOK | NA | NA | 25087000 | 498713000 | 523800000 | 93587000 | NA | NA | 115868000 | NA | NA | 444959000 | -14746000 | NA | NA | NA | NA | NA | 81052000 | NA | NA | NA | NA | NA | 66306000 | NA | 66306000 | NA | NA | NA | 66306000 | NA | NA | NA | NA | NA | NA | NA | 21506000 | NA | NA | 15691000 | 15691000 | 150000 | NA | 35000 | 5630000 | 5815000 | NA | 11628000 | NA | 49205000 | 1217305000 | 271319000 | 271319000 | 1537829000 | 1559335000 | 1e+08 | NA | 100000000 | 830838000 | 830838000 | 930837000 | 311562000 | NA | NA | NA | NA | NA | 311562000 | NA | 12634000 | 10216000 | NA | 294085000 | 316936000 | 628498000 | 1559335000 | NA | NA |
| 2019 | NOK | NA | NA | 24310000 | 532437000 | 556747000 | 91597000 | NA | NA | 112039000 | NA | NA | 440284000 | 24866000 | NA | NA | NA | NA | NA | 228726000 | NA | NA | NA | NA | NA | 253592000 | NA | 253592000 | NA | NA | NA | 253592000 | NA | NA | NA | NA | NA | NA | NA | 22309000 | NA | NA | 16494000 | 16494000 | 150000 | NA | 35000 | 5630000 | 5815000 | NA | 11521000 | NA | 54847000 | 1137580000 | 231742000 | 231742000 | 1424170000 | 1446478000 | 1e+08 | NA | 100000000 | 764531000 | 764531000 | 864531000 | 310847000 | NA | NA | NA | NA | NA | 310847000 | NA | 10582000 | 9942000 | NA | 250577000 | 271101000 | 581948000 | 1446478000 | 3378000 | NA |
| 2018 | NOK | NA | NA | 27289000 | 519161000 | 546450000 | 88546000 | NA | NA | 112606000 | NA | NA | 454090000 | 3814000 | NA | NA | NA | NA | NA | -13506000 | NA | NA | NA | NA | NA | -9693000 | NA | -9693000 | NA | NA | NA | -9693000 | NA | NA | NA | NA | NA | NA | NA | 21854000 | NA | NA | 21669000 | 21669000 | 150000 | NA | 35000 | NA | 185000 | NA | 15722000 | NA | 66629000 | 935205000 | 150494000 | 150494000 | 1152329000 | 1174182000 | 1e+08 | NA | 100000000 | 510939000 | 510939000 | 610939000 | 288223000 | NA | NA | NA | NA | NA | 288223000 | NA | 14834000 | 9847000 | NA | 250340000 | 275020000 | 563244000 | 1174182000 | NA | NA |
| 2017 | NOK | 1505000 | 72000 | 23536000 | 496077000 | 519613000 | 85718000 | NA | NA | 107077000 | NA | NA | 469167000 | -35272000 | NA | NA | NA | NA | NA | 94862000 | NA | NA | NA | NA | 0 | 59589000 | NA | 59589000 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 22920000 | NA | NA | 22735000 | 22735000 | 150000 | NA | 35000 | NA | 185000 | NA | 12131000 | NA | 34029000 | 990189000 | 130097000 | 130097000 | 1154316000 | 1177236000 | 1e+08 | NA | 100000000 | 520632000 | 520632000 | 620632000 | 263783000 | NA | NA | NA | NA | NA | 263783000 | NA | 10376000 | 9728000 | NA | 272717000 | 292821000 | 556604000 | 1177236000 | NA | NA |
| 2016 | NOK | 1437000 | 47000 | 22755000 | 482634000 | 505389000 | 79496000 | NA | NA | 108231000 | NA | NA | 490831000 | -64938000 | NA | NA | NA | NA | NA | 55987000 | NA | NA | NA | NA | 0 | -8952000 | NA | -8952000 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 17641000 | 814000 | NA | 16642000 | 17456000 | 150000 | NA | 35000 | NA | 185000 | NA | 15676000 | NA | 19934000 | 951779000 | 134479000 | 134479000 | 1106192000 | 1123833000 | 1e+08 | NA | 100000000 | 453860000 | 453860000 | 553860000 | 525846000 | NA | NA | NA | NA | NA | 525846000 | NA | 17028000 | 10214000 | NA | 16885000 | 44127000 | 569973000 | 1123833000 | NA | NA |
| 2015 | NOK | 1363000 | 40000 | 23232000 | 476820000 | 500052000 | 83519000 | NA | NA | 105407000 | NA | NA | 505227000 | -88694000 | NA | NA | NA | NA | NA | 39781000 | NA | NA | NA | NA | 0 | -48914000 | NA | -48914000 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 17010000 | 814000 | NA | 16011000 | 16825000 | 150000 | NA | 35000 | NA | 185000 | NA | 10882000 | NA | 17167000 | 901854000 | 201194000 | 201194000 | 1120215000 | 1137225000 | 1e+08 | NA | 100000000 | 462811000 | 462811000 | 562812000 | 533370000 | NA | NA | NA | NA | NA | 533370000 | NA | 15284000 | 9407000 | NA | 16353000 | 41043000 | 574413000 | 1137225000 | NA | NA |
| 2014 | NOK | 1292000 | 15000 | 23719000 | 453171000 | 476890000 | 68641000 | NA | NA | 96612000 | NA | NA | 479130000 | -70881000 | NA | NA | NA | NA | NA | 74519000 | NA | NA | NA | NA | 0 | 3637000 | NA | 3637000 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 9420000 | 814000 | NA | 8421000 | 9235000 | 150000 | NA | 35000 | NA | 185000 | NA | 16569000 | NA | 21411000 | 894542000 | 211626000 | 211626000 | 1127579000 | 1136999000 | 1e+08 | NA | 100000000 | 511726000 | 511726000 | 611726000 | 487611000 | NA | NA | NA | NA | NA | 487611000 | NA | 10741000 | 9788000 | NA | 17134000 | 37662000 | 525273000 | 1136999000 | NA | NA |
| 2013 | NOK | 1262000 | 36000 | 20627000 | 415352000 | 435979000 | 67069000 | NA | NA | 91847000 | NA | NA | 456530000 | -87620000 | NA | NA | NA | NA | NA | 90198000 | NA | NA | NA | NA | 0 | 2578000 | NA | 2578000 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 10481000 | 814000 | NA | 9482000 | 10296000 | 150000 | NA | 35000 | NA | 185000 | NA | 14431000 | NA | 18108000 | 863275000 | 188552000 | 188552000 | 1069936000 | 1080417000 | 1e+08 | NA | 100000000 | 508089000 | 508089000 | 608089000 | 430391000 | NA | NA | NA | NA | NA | 430391000 | NA | 9170000 | 9471000 | NA | 23296000 | 41938000 | 472329000 | 1080417000 | NA | NA |
| 2012 | NOK | 1237000 | 35000 | 21045000 | 395441000 | 416486000 | 64434000 | NA | NA | 93625000 | NA | NA | 414992000 | -62940000 | NA | NA | NA | NA | NA | 73723000 | NA | NA | NA | NA | 0 | 10781000 | NA | 10781000 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 11153000 | 814000 | NA | 10154000 | 10968000 | 150000 | NA | 35000 | NA | 185000 | NA | 14936000 | NA | 20183000 | 775659000 | 201486000 | 201486000 | 997328000 | 1008481000 | 1e+08 | NA | 100000000 | 505511000 | 505511000 | 605510000 | 362848000 | NA | NA | NA | NA | NA | 362848000 | NA | 6076000 | 9765000 | NA | 24282000 | 40123000 | 402971000 | 1008481000 | NA | NA |
| 2011 | NOK | NA | NA | 20550000 | 375158000 | 395708000 | 70966000 | NA | NA | NA | NA | NA | 381303000 | -56561000 | NA | NA | NA | NA | NA | -48265000 | NA | NA | NA | NA | 0 | -104826000 | NA | -104826000 | NA | NA | NA | -104826000 | NA | NA | NA | NA | NA | NA | NA | 11540000 | 814000 | NA | 10541000 | 11355000 | 150000 | NA | 35000 | NA | 185000 | NA | 12336000 | NA | 19275000 | 705586000 | 138595000 | 138595000 | 863456000 | 874996000 | 1e+08 | NA | 100000000 | 494729000 | 494729000 | 594729000 | 243204000 | NA | NA | NA | NA | NA | 243204000 | NA | 10856000 | 8772000 | NA | 17436000 | 37064000 | 280268000 | 874996000 | NA | NA |
| 2010 | NOK | NA | NA | 18898000 | 394270000 | 413168000 | 59821000 | NA | NA | NA | NA | NA | 319848000 | 33499000 | NA | NA | NA | NA | NA | 101511000 | NA | NA | NA | NA | 0 | 135010000 | NA | 135010000 | NA | NA | NA | 135010000 | NA | NA | NA | NA | NA | NA | NA | 13252000 | 814000 | NA | 12253000 | 13067000 | NA | NA | NA | 185000 | 185000 | 106000 | NA | NA | 40719000 | 817283000 | 134792000 | 134792000 | 992900000 | 1006152000 | 1e+08 | NA | 100000000 | 599555000 | 599555000 | 699555000 | 271353000 | NA | NA | NA | NA | NA | 271353000 | NA | 8796000 | 8228000 | NA | 18220000 | 35245000 | 306598000 | 1006152000 | NA | NA |
| 2009 | NOK | 1154000 | 44000 | 17115000 | 483182000 | 500297000 | 0 | 0 | 0 | 78694000 | 0 | NA | 416120000 | 84177000 | 0 | 0 | 0 | 0 | 0 | 0 | NA | 0 | NA | 0 | 0 | 84177000 | 0 | 84177000 | NA | NA | 0 | 84177000 | NA | NA | NA | NA | NA | NA | 0 | 26970000 | 1189000 | NA | 15546000 | 16735000 | NA | 0 | NA | 10235000 | 10235000 | 0 | NA | NA | 20268000 | 749521000 | 104376000 | 104376000 | 874165000 | 901135000 | NA | 100000000 | 100000000 | 464545000 | 464545000 | 564545000 | 289251000 | NA | NA | NA | 0 | NA | 289251000 | NA | 16133000 | 7846000 | NA | 23361000 | 47340000 | 336590000 | 901135000 | NA | NA |
| 2008 | NOK | NA | NA | 16917000 | 115787000 | 132704000 | 0 | 0 | 0 | 73252000 | 0 | NA | 380021000 | -247317000 | 0 | 0 | 0 | 0 | 0 | 0 | NA | 0 | NA | 0 | 0 | -247317000 | 0 | -247317000 | NA | NA | 0 | -247317000 | NA | NA | NA | NA | NA | NA | 0 | 33920000 | 2727000 | NA | 16185000 | 18912000 | NA | 0 | NA | 15008000 | 15008000 | 0 | NA | NA | 44538000 | 667011000 | 73102000 | 73102000 | 784651000 | 818571000 | NA | 123481000 | 123481000 | 356887000 | 356887000 | 480368000 | 290048000 | NA | NA | NA | 0 | NA | 290048000 | NA | 14645000 | 7249000 | NA | 26262000 | 48156000 | 338203000 | 818571000 | NA | NA |
| 2007 | NOK | 936000 | 52000 | 86715000 | 345348000 | 432062000 | 0 | 0 | 0 | 85553000 | 0 | NA | 168152000 | 263910000 | 0 | 0 | 0 | 0 | 0 | 0 | NA | 0 | 150438000 | 150438000 | 150438000 | 113472000 | 0 | 113472000 | NA | NA | 0 | 113472000 | NA | NA | NA | NA | NA | NA | 0 | 29579000 | 540000 | NA | 13346000 | 13886000 | NA | 0 | NA | 15693000 | 15693000 | 0 | NA | NA | 50797000 | 941052000 | 70731000 | 70731000 | 1062580000 | 1092159000 | NA | 125902000 | 125902000 | 601783000 | 601783000 | 727685000 | 324319000 | NA | NA | NA | 0 | NA | 324319000 | NA | 8376000 | 6850000 | NA | 24930000 | 40155000 | 364474000 | 1092159000 | NA | NA |
| 2004 | NOK | 896000 | NA | 1484000 | 364926000 | 366410000 | 1805000 | NA | 177032000 | 150280000 | 3143000 | NA | 121399000 | 63031000 | NA | NA | NA | NA | 7713000 | 7713000 | NA | NA | NA | 0 | -42762000 | 113506000 | NA | 113506000 | NA | NA | NA | 113506000 | NA | NA | NA | NA | NA | NA | 0 | 136818000 | 3340000 | NA | 11273000 | 14613000 | NA | NA | NA | NA | 122205000 | 647000 | 0 | NA | 6113000 | 545950000 | 111010000 | NA | 663720000 | 800538000 | 1e+08 | NA | 100000000 | 483604000 | 483604000 | 583604000 | 155467000 | NA | NA | NA | 155467000 | NA | 155467000 | NA | 14621000 | 29038000 | NA | 17808000 | 61467000 | 216934000 | 800538000 | NA | NA |
| 2003 | NOK | NA | NA | NA | 352040000 | 352040000 | NA | NA | 172320000 | NA | 2268000 | NA | 138773000 | 38679000 | NA | NA | NA | NA | 9512000 | 9512000 | NA | NA | NA | 972000 | -69550000 | 117741000 | NA | 117741000 | NA | NA | NA | 117741000 | NA | NA | NA | NA | NA | NA | 0 | 122795000 | 3068000 | NA | 7888000 | 10956000 | NA | NA | NA | NA | 111839000 | 1441000 | 0 | NA | 2439000 | 448556000 | 103252000 | NA | 555688000 | 678483000 | 1e+08 | NA | 100000000 | 370097000 | 370097000 | 470097000 | 151041000 | NA | NA | NA | 151041000 | NA | 151041000 | NA | 14285000 | 26523000 | NA | 16537000 | 57345000 | 208386000 | 678483000 | NA | NA |
| 2002 | NOK | 1023000 | NA | 5935000 | 360855000 | 366790000 | 4352000 | NA | 169552000 | 144086000 | 2935000 | NA | 143380000 | 46571000 | NA | NA | NA | NA | 2708000 | 2708000 | NA | NA | NA | 304000 | 68115000 | -18836000 | 0 | -18836000 | NA | NA | NA | -18836000 | NA | NA | NA | NA | NA | NA | 0 | 123788000 | 3068000 | NA | 4356000 | 7424000 | NA | NA | NA | NA | 116364000 | 2222000 | 0 | NA | 9154000 | 348668000 | 75658000 | NA | 435702000 | 559490000 | 1e+08 | NA | 100000000 | 252356000 | 252356000 | 352356000 | 151298000 | NA | NA | NA | 151298000 | NA | 151298000 | NA | 9104000 | 28167000 | NA | 18565000 | 55836000 | 207134000 | 559490000 | NA | NA |
kreft_excel| year | firm | revenue_private_contributions | revenue_public_grants | revenue_operational_activities | revenue_membership_fees | revenue_commercial_activities | revenue_investments | revenue_other | expenses_program_services | expenses_administrative | expenses_fundraising | expenses_other | assets_property_plant_equipment_and_intangible_assets | assets_long_term_investments | assets_long_term_receivables | assets_other_non_current_assets | assets_cash_in_bank_and_cash_equivalents | assets_short_term_receivables | assets_short_term_investments | assets_other_current_assets | liability_short_term_grants_payable | liability_revocable_endowments | liability_other_current_liabilitues | liability_long_term_grants_payable | liability_other_non_current_liabilities | equity_equity |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2020 | Kreftforeningen | 462196000 | 29148000 | 4806000 | 25087000 | 0 | 81052000 | 2563000 | 434431000 | 10528000 | 93587000 | 1000 | 15691000 | 185000 | 5630000 | 0 | 0 | 271319000 | 48408000 | 1218102000 | 272786000 | 7498000 | 36650000 | 301822000 | 9741000 | 930837000 |
| 2019 | Kreftforeningen | 496545000 | 28570000 | 4574000 | 24309000 | 0 | 228726000 | 2748000 | 430086000 | 10198000 | 91596000 | 1000 | 16494000 | 185000 | 5630000 | 0 | 0 | 231742000 | 54012000 | 1138415000 | 233486000 | 2313000 | 35302000 | 301842000 | 9005000 | 864530000 |
Testing
Data manipulation
kreft_excel |>
filter(year == max(year))| year | firm | revenue_private_contributions | revenue_public_grants | revenue_operational_activities | revenue_membership_fees | revenue_commercial_activities | revenue_investments | revenue_other | expenses_program_services | expenses_administrative | expenses_fundraising | expenses_other | assets_property_plant_equipment_and_intangible_assets | assets_long_term_investments | assets_long_term_receivables | assets_other_non_current_assets | assets_cash_in_bank_and_cash_equivalents | assets_short_term_receivables | assets_short_term_investments | assets_other_current_assets | liability_short_term_grants_payable | liability_revocable_endowments | liability_other_current_liabilitues | liability_long_term_grants_payable | liability_other_non_current_liabilities | equity_equity |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2020 | Kreftforeningen | 462196000 | 29148000 | 4806000 | 25087000 | 0 | 81052000 | 2563000 | 434431000 | 10528000 | 93587000 | 1000 | 15691000 | 185000 | 5630000 | 0 | 0 | 271319000 | 48408000 | 1218102000 | 272786000 | 7498000 | 36650000 | 301822000 | 9741000 | 930837000 |
kreft_html |>
filter(year == max(kreft_excel$year))| year | valutakode | lederlonn | leder_annen_godtgjorelse | sum_salgsinntekter | annen_driftsinntekt | sum_driftsinntekter | varekostnad | beholdningsendringer | lonnskostnader | herav_kun_lonn | ordinaere_avskrivninger | nedskrivning | andre_driftskostnader | driftsresultat | inntekt_pa_invest_annet_foretak_i_sm_konsern | inntekt_pa_investering_i_datterselskap | sum_annen_renteinntekt | inntekt_pa_invest_i_tilknyttet_selskap | sum_annen_finansinntekt | sum_finansinntekter | nedskrivning_fin_anleggsmidler | sum_annen_rentekostnad | andre_finanskostnader | sum_annen_finanskostnad | sum_finanskostnader | resultat_for_skatt | sum_skatt | ordinaert_resultat | ekstraordinaere_inntekter | ekstraordinaere_kostnader | skatt_ekstraordinaert | arsresultat | ordinaert_utbytte | ekstraordinaert_utbytte | tilleggsutbytte | sum_utbytte | konsernbidrag | goodwill | sum_immaterielle_midler | sum_anleggsmidler | tomter_bygninger_og_annen_fast_eiendom | maskiner_anlegg_biler | driftslosore_inventar_verktoy_biler | sum_varige_driftsmidler | aksjer_investeringer_i_datterselskap | endr_behold_varer_under_tilvirk_ferdige | investeringer_i_aksjer_og_andeler | andre_fordringer | sum_finansielle_anleggsmidler | sum_varelager | kundefordringer | konsernfordringer | sum_fordringer | sum_investeringer | kasse_bank_post | sum_kasse_bank_post | sum_omlopsmidler | sum_eiendeler | aksje_selskapskapital | annen_innskutt_egenkapital | sum_innskutt_egenkapital | sum_opptjent_egenkapital | annen_egenkapital | sum_egenkapital | sum_avsetninger_til_forpliktelser | pant_gjeld_til_kredittinstitusjoner | langsiktig_konserngjeld | ansvarlig_lanekapital | sum_annen_langsiktig_gjeld | annen_langsiktig_gjeld | sum_langsiktig_gjeld | gjeld_til_kredittinstitusjoner | leverandorgjeld | skyldig_offentlige_avgifter | kortsiktig_konserngjeld | annen_kortsiktig_gjeld | sum_kortsiktig_gjeld | sum_gjeld | sum_egenkapital_og_gjeld | garantistillelser | pantstillelser |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2020 | NOK | NA | NA | 25087000 | 498713000 | 523800000 | 93587000 | NA | NA | 115868000 | NA | NA | 444959000 | -14746000 | NA | NA | NA | NA | NA | 81052000 | NA | NA | NA | NA | NA | 66306000 | NA | 66306000 | NA | NA | NA | 66306000 | NA | NA | NA | NA | NA | NA | NA | 21506000 | NA | NA | 15691000 | 15691000 | 150000 | NA | 35000 | 5630000 | 5815000 | NA | 11628000 | NA | 49205000 | 1217305000 | 271319000 | 271319000 | 1537829000 | 1559335000 | 1e+08 | NA | 1e+08 | 830838000 | 830838000 | 930837000 | 311562000 | NA | NA | NA | NA | NA | 311562000 | NA | 12634000 | 10216000 | NA | 294085000 | 316936000 | 628498000 | 1559335000 | NA | NA |
kreft_excel |>
left_join(kreft_html, by = "year")| year | firm | revenue_private_contributions | revenue_public_grants | revenue_operational_activities | revenue_membership_fees | revenue_commercial_activities | revenue_investments | revenue_other | expenses_program_services | expenses_administrative | expenses_fundraising | expenses_other | assets_property_plant_equipment_and_intangible_assets | assets_long_term_investments | assets_long_term_receivables | assets_other_non_current_assets | assets_cash_in_bank_and_cash_equivalents | assets_short_term_receivables | assets_short_term_investments | assets_other_current_assets | liability_short_term_grants_payable | liability_revocable_endowments | liability_other_current_liabilitues | liability_long_term_grants_payable | liability_other_non_current_liabilities | equity_equity | valutakode | lederlonn | leder_annen_godtgjorelse | sum_salgsinntekter | annen_driftsinntekt | sum_driftsinntekter | varekostnad | beholdningsendringer | lonnskostnader | herav_kun_lonn | ordinaere_avskrivninger | nedskrivning | andre_driftskostnader | driftsresultat | inntekt_pa_invest_annet_foretak_i_sm_konsern | inntekt_pa_investering_i_datterselskap | sum_annen_renteinntekt | inntekt_pa_invest_i_tilknyttet_selskap | sum_annen_finansinntekt | sum_finansinntekter | nedskrivning_fin_anleggsmidler | sum_annen_rentekostnad | andre_finanskostnader | sum_annen_finanskostnad | sum_finanskostnader | resultat_for_skatt | sum_skatt | ordinaert_resultat | ekstraordinaere_inntekter | ekstraordinaere_kostnader | skatt_ekstraordinaert | arsresultat | ordinaert_utbytte | ekstraordinaert_utbytte | tilleggsutbytte | sum_utbytte | konsernbidrag | goodwill | sum_immaterielle_midler | sum_anleggsmidler | tomter_bygninger_og_annen_fast_eiendom | maskiner_anlegg_biler | driftslosore_inventar_verktoy_biler | sum_varige_driftsmidler | aksjer_investeringer_i_datterselskap | endr_behold_varer_under_tilvirk_ferdige | investeringer_i_aksjer_og_andeler | andre_fordringer | sum_finansielle_anleggsmidler | sum_varelager | kundefordringer | konsernfordringer | sum_fordringer | sum_investeringer | kasse_bank_post | sum_kasse_bank_post | sum_omlopsmidler | sum_eiendeler | aksje_selskapskapital | annen_innskutt_egenkapital | sum_innskutt_egenkapital | sum_opptjent_egenkapital | annen_egenkapital | sum_egenkapital | sum_avsetninger_til_forpliktelser | pant_gjeld_til_kredittinstitusjoner | langsiktig_konserngjeld | ansvarlig_lanekapital | sum_annen_langsiktig_gjeld | annen_langsiktig_gjeld | sum_langsiktig_gjeld | gjeld_til_kredittinstitusjoner | leverandorgjeld | skyldig_offentlige_avgifter | kortsiktig_konserngjeld | annen_kortsiktig_gjeld | sum_kortsiktig_gjeld | sum_gjeld | sum_egenkapital_og_gjeld | garantistillelser | pantstillelser |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2020 | Kreftforeningen | 462196000 | 29148000 | 4806000 | 25087000 | 0 | 81052000 | 2563000 | 434431000 | 10528000 | 93587000 | 1000 | 15691000 | 185000 | 5630000 | 0 | 0 | 271319000 | 48408000 | 1218102000 | 272786000 | 7498000 | 36650000 | 301822000 | 9741000 | 930837000 | NOK | NA | NA | 25087000 | 498713000 | 523800000 | 93587000 | NA | NA | 115868000 | NA | NA | 444959000 | -14746000 | NA | NA | NA | NA | NA | 81052000 | NA | NA | NA | NA | NA | 66306000 | NA | 66306000 | NA | NA | NA | 66306000 | NA | NA | NA | NA | NA | NA | NA | 21506000 | NA | NA | 15691000 | 15691000 | 150000 | NA | 35000 | 5630000 | 5815000 | NA | 11628000 | NA | 49205000 | 1217305000 | 271319000 | 271319000 | 1537829000 | 1559335000 | 1e+08 | NA | 1e+08 | 830838000 | 830838000 | 930837000 | 311562000 | NA | NA | NA | NA | NA | 311562000 | NA | 12634000 | 10216000 | NA | 294085000 | 316936000 | 628498000 | 1559335000 | NA | NA |
| 2019 | Kreftforeningen | 496545000 | 28570000 | 4574000 | 24309000 | 0 | 228726000 | 2748000 | 430086000 | 10198000 | 91596000 | 1000 | 16494000 | 185000 | 5630000 | 0 | 0 | 231742000 | 54012000 | 1138415000 | 233486000 | 2313000 | 35302000 | 301842000 | 9005000 | 864530000 | NOK | NA | NA | 24310000 | 532437000 | 556747000 | 91597000 | NA | NA | 112039000 | NA | NA | 440284000 | 24866000 | NA | NA | NA | NA | NA | 228726000 | NA | NA | NA | NA | NA | 253592000 | NA | 253592000 | NA | NA | NA | 253592000 | NA | NA | NA | NA | NA | NA | NA | 22309000 | NA | NA | 16494000 | 16494000 | 150000 | NA | 35000 | 5630000 | 5815000 | NA | 11521000 | NA | 54847000 | 1137580000 | 231742000 | 231742000 | 1424170000 | 1446478000 | 1e+08 | NA | 1e+08 | 764531000 | 764531000 | 864531000 | 310847000 | NA | NA | NA | NA | NA | 310847000 | NA | 10582000 | 9942000 | NA | 250577000 | 271101000 | 581948000 | 1446478000 | 3378000 | NA |
Visualisation ideas
CEO compensation and currency testing
compensation_p <- kreft_html |>
group_by(year) |>
mutate(
leder_total = sum(lederlonn, leder_annen_godtgjorelse, na.rm = TRUE)
) |>
ggplot(aes(year, leder_total, fill = leder_total)) +
geom_col() +
scale_y_continuous(labels = scales::label_dollar(prefix = "")) +
labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation")
ggplotly(compensation_p)compensation_p2 <- kreft_html |>
select(
year, lederlonn, leder_annen_godtgjorelse
) |>
rename(
Year = year,
Salary = lederlonn,
`Other compensation` = leder_annen_godtgjorelse
) |>
pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |>
ggplot(aes(Year, value, fill = Compensation)) +
geom_col() +
scale_y_continuous(
labels = scales::label_dollar(prefix =
str_c(kreft_html$valutakode[1], " "))) +
scale_fill_met_d("Troy") +
labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation")
ggplotly(compensation_p2)## Warning: Removed 18 rows containing missing values (position_stack).# if else structure to check for uniform currency code?
length(unique(kreft_html$valutakode)) == 1## [1] TRUE# Testing with uniform currency
if (length(unique(kreft_html$valutakode)) == 1) {
kreft_html |>
select(
year, lederlonn, leder_annen_godtgjorelse
) |>
rename(
Year = year,
Salary = lederlonn,
`Other compensation` = leder_annen_godtgjorelse
) |>
pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |>
ggplot(aes(Year, value, fill = Compensation)) +
geom_col() +
scale_y_continuous(
labels = scales::label_dollar(prefix =
str_c(kreft_html$valutakode[1], " "))) +
scale_fill_met_d("Troy") +
labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation")
} else {
kreft_html |>
select(
year, lederlonn, leder_annen_godtgjorelse
) |>
rename(
Year = year,
Salary = lederlonn,
`Other compensation` = leder_annen_godtgjorelse
) |>
pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |>
ggplot(aes(Year, value, fill = Compensation)) +
geom_col() +
scale_y_continuous(
labels = scales::label_dollar(prefix = "? ")) +
scale_fill_met_d("Troy") +
labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation",
subtitle = "Note: not all values are denominated in the same currency")
}## Warning: Removed 18 rows containing missing values (position_stack).
# Creating data frame with multiple currencies
currency_test <- kreft_html |>
mutate(
valutakode = ifelse(year == 2019, "USD", valutakode)
)
# Testing new data frame
if (length(unique(currency_test$valutakode)) == 1) {
currency_test |>
select(
year, lederlonn, leder_annen_godtgjorelse
) |>
rename(
Year = year,
Salary = lederlonn,
`Other compensation` = leder_annen_godtgjorelse
) |>
pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |>
ggplot(aes(Year, value, fill = Compensation)) +
geom_col() +
scale_y_continuous(
labels = scales::label_dollar(prefix =
str_c(kreft_html$valutakode[1], " "))) +
scale_fill_met_d("Troy") +
labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation")
} else {
currency_test |>
select(
year, lederlonn, leder_annen_godtgjorelse
) |>
rename(
Year = year,
Salary = lederlonn,
`Other compensation` = leder_annen_godtgjorelse
) |>
pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |>
ggplot(aes(Year, value, fill = Compensation)) +
geom_col() +
scale_y_continuous(
labels = scales::label_dollar(prefix = "? ")) +
scale_fill_met_d("Troy") +
labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation",
subtitle = "Note: not all values are denominated in the same currency")
}## Warning: Removed 18 rows containing missing values (position_stack).
# Could probably be done with two internal ifelse() functions
# ifelse() version
# Testing
currency_test |>
select(
year, lederlonn, leder_annen_godtgjorelse
) |>
rename(
Year = year,
Salary = lederlonn,
`Other compensation` = leder_annen_godtgjorelse
) |>
pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |>
ggplot(aes(Year, value, fill = Compensation)) +
geom_col() +
scale_y_continuous(
labels = ifelse(length(unique(currency_test$valutakode)) == 1,
label_dollar(prefix = str_c(kreft_html$valutakode[1], " ")),
label_dollar(prefix = "? "))
) +
scale_fill_met_d("Troy") +
labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation",
subtitle = ifelse(length(unique(currency_test$valutakode)) == 1,
element_blank(),
"Note: not all values are denominated in the same currency"))## Warning: Removed 18 rows containing missing values (position_stack).
# Original
kreft_html |>
select(
year, lederlonn, leder_annen_godtgjorelse
) |>
rename(
Year = year,
Salary = lederlonn,
`Other compensation` = leder_annen_godtgjorelse
) |>
pivot_longer(c(Salary, `Other compensation`), names_to = "Compensation") |>
ggplot(aes(Year, value, fill = Compensation)) +
geom_col() +
scale_y_continuous(
labels = ifelse(length(unique(kreft_html$valutakode)) == 1,
label_dollar(prefix = str_c(kreft_html$valutakode[1], " ")),
label_dollar(prefix = "? "))
) +
scale_fill_met_d("Troy") +
labs(x = "Year", y = "Total compensation", title = "Annual CEO compensation",
subtitle = ifelse(length(unique(kreft_html$valutakode)) == 1,
"",
"Note: not all values are denominated in the same currency"))## Warning: Removed 18 rows containing missing values (position_stack).
Result
arsresultat_p <- kreft_html |>
mutate(
profit = ifelse(arsresultat >= 0, TRUE, FALSE)
) |>
ggplot(aes(year, arsresultat, fill = profit)) +
geom_col() +
scale_y_continuous(labels = scales::label_dollar(prefix = ""))
ggplotly(arsresultat_p)## Warning: Removed 6 rows containing missing values (position_stack).arsresultat_p_met <- kreft_html |>
mutate(
profit = ifelse(arsresultat >= 0, TRUE, FALSE)
) |>
ggplot(aes(year, arsresultat, fill = profit)) +
geom_col() +
scale_y_continuous(labels = scales::label_dollar(prefix = "")) +
scale_fill_met_d("Tsimshian")
ggplotly(arsresultat_p_met)## Warning: Removed 6 rows containing missing values (position_stack).ordinaert_p <- kreft_html |>
mutate(
profit = ifelse(ordinaert_resultat >= 0, TRUE, FALSE)
) |>
ggplot(aes(year, ordinaert_resultat, fill = profit)) +
geom_col() +
scale_y_continuous(labels = scales::label_dollar(prefix = "")) +
theme(legend.position = "none")
ggplotly(ordinaert_p)ordinaert_p_met <- kreft_html |>
mutate(
profit = ifelse(ordinaert_resultat >= 0, TRUE, FALSE)
) |>
ggplot(aes(year, ordinaert_resultat, fill = profit)) +
geom_col() +
scale_y_continuous(labels = scales::label_dollar(prefix = "")) +
theme(legend.position = "none") +
scale_fill_met_d("Tsimshian")
ggplotly(ordinaert_p_met)KPI ideas
Fundraising ROI
kreft_excel |>
mutate(
KPI_ROI = expenses_fundraising / (revenue_private_contributions + revenue_membership_fees)
) |>
select(year, KPI_ROI)| year | KPI_ROI |
|---|---|
| 2020 | 0.1920588 |
| 2019 | 0.1758573 |
Administrative expenses
kreft_excel |>
mutate(
KPI_admin =
str_c(
format(
(expenses_administrative / expenses_program_services *100), digits = 3
), "%"
)
) |>
select(year, KPI_admin)| year | KPI_admin |
|---|---|
| 2020 | 2.42% |
| 2019 | 2.37% |
kreft_excel |>
mutate(
KPI_admin =
str_c(
format(
(expenses_administrative / expenses_program_services *100), digits = 3
), "%"
)
) |>
filter(year == max(year)) |>
select(KPI_admin) |>
str_flatten()## [1] "2.42%"kreft_excel |> mutate(
KPI_admin =
expenses_administrative / expenses_program_services *100) |>
filter(year == max(year)) |>
select(KPI_admin)| KPI_admin |
|---|
| 2.4234 |